﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    using Model;
    using System.Data;
    using System.Data.SqlClient;
    public class DALView_Stu_Class
    {
        /// <summary>
        /// 分页查询所有数据显示
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="Index"></param>
        /// <returns></returns>
        public List<View_Stu_Class> Proc_SelectPage(int PageSize, int Index)
        {
            string Pro_name = @"[dbo].[PS_proc_CommonPager]";
            List<View_Stu_Class> list = new List<View_Stu_Class>();
            List<SqlParameter> sps = new List<SqlParameter>() {
                new SqlParameter("@tblName","View_Stu_Class"),
                new SqlParameter("@fldName","ID"),
                new SqlParameter("@PageSize",PageSize),
                new SqlParameter("@PageIndex",Index),
                new SqlParameter("@IsCount",0),
                new SqlParameter("@OrderType",0),
                new SqlParameter("@strWhere",""),
            };
            using (SqlDataReader sdr = DBHelper.Proc_ExecuteReader(Pro_name, sps.ToArray()))
            {
                while (sdr.Read())
                {
                    list.Add(new View_Stu_Class()
                    {
                        Birthday = sdr["Birthday"] == DBNull.Value ?
                        (DateTime?)null : Convert.ToDateTime(sdr["Birthday"]),
                        CID = sdr["CID"] == DBNull.Value ?
                        (int?)null : Convert.ToInt32(sdr["CID"]),
                        CName = sdr["CName"] == DBNull.Value ?
                        null : Convert.ToString(sdr["CName"]),
                        Height = sdr["Height"] == DBNull.Value ?
                        (double?)null : Convert.ToDouble(sdr["Height"]),
                        ID = sdr["ID"] == DBNull.Value ?
                        (int?)null : Convert.ToInt32(sdr["ID"]),
                        Name = sdr["Name"] == DBNull.Value ?
                        null : Convert.ToString(sdr["Name"]),
                        Readed = sdr["Readed"] == DBNull.Value ?
                        (bool?)null : Convert.ToBoolean(sdr["Readed"]),
                        Sex = sdr["Sex"] == DBNull.Value ?
                        null : Convert.ToString(sdr["Sex"]),
                    });
                }
            }
            return list;
        }

        /// <summary>
        /// 查询所有总记录数
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="Index"></param>
        /// <param name="k"></param>
        /// <returns></returns>
        public int Proc_SelectPageCount()
        {
            string Pro_name = @"[dbo].[PS_proc_CommonPager]";
            List<SqlParameter> sps = new List<SqlParameter>() {
                new SqlParameter("@tblName","View_Stu_Class"),
                new SqlParameter("@fldName","ID"),
                new SqlParameter("@PageSize",0),
                new SqlParameter("@PageIndex",0),
                new SqlParameter("@IsCount",1),
                new SqlParameter("@OrderType",0),
                new SqlParameter("@strWhere",""),
            };
            object obj = DBHelper.Proc_ExecuteScalar(Pro_name, sps.ToArray());
            return Convert.ToInt32(obj);

        }
        /// <summary>
        /// 条件查询分页时显示数据
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="Index"></param>
        /// <param name="stu"></param>
        /// <param name="sdate"></param>
        /// <param name="edate"></param>
        /// <returns></returns>
        public List<View_Stu_Class> Proc_SelectPageWhere(int PageSize, int Index, View_Stu_Class stu, string sdate, string edate)
        {
            string Pro_name = @"[dbo].[PS_proc_CommonPager]";
            List<View_Stu_Class> list = new List<View_Stu_Class>();
            List<SqlParameter> sps = new List<SqlParameter>() {
                new SqlParameter("@tblName","View_Stu_Class"),
                new SqlParameter("@fldName","ID"),
                new SqlParameter("@PageSize",PageSize),
                new SqlParameter("@PageIndex",Index),
                new SqlParameter("@IsCount",0),
                new SqlParameter("@OrderType",0),
            };
            StringBuilder Where = new StringBuilder("");
            if (stu != null)
            {
                if (!string.IsNullOrEmpty(stu.Name))
                {
                    Where.AppendFormat(" and name like '%{0}%'",stu.Name);
                }//字符串列类型判断
                if (!string.IsNullOrEmpty(stu.Sex))
                {
                    Where.AppendFormat(" and sex='{0}'",stu.Sex);
                }//数字类型判断
                if (stu.CID != null)
                {
                    Where.AppendFormat(" and CID='{0}'",stu.CID);
                }
            }
            if (!string.IsNullOrEmpty(sdate) && !string.IsNullOrEmpty(edate))
            {
                Where.AppendFormat(" and Birthday between '{0}' and '{1}'",sdate,edate);
            }
            sps.Add(new SqlParameter("@strWhere", Where.ToString()));
            using (SqlDataReader sdr = DBHelper.Proc_ExecuteReader(Pro_name, sps.ToArray()))
            {
                while (sdr.Read())
                {
                    list.Add(new View_Stu_Class()
                    {
                        Birthday = sdr["Birthday"] == DBNull.Value ?
                        (DateTime?)null : Convert.ToDateTime(sdr["Birthday"]),
                        CID = sdr["CID"] == DBNull.Value ?
                        (int?)null : Convert.ToInt32(sdr["CID"]),
                        CName = sdr["CName"] == DBNull.Value ?
                        null : Convert.ToString(sdr["CName"]),
                        Height = sdr["Height"] == DBNull.Value ?
                        (double?)null : Convert.ToDouble(sdr["Height"]),
                        ID = sdr["ID"] == DBNull.Value ?
                        (int?)null : Convert.ToInt32(sdr["ID"]),
                        Name = sdr["Name"] == DBNull.Value ?
                        null : Convert.ToString(sdr["Name"]),
                        Readed = sdr["Readed"] == DBNull.Value ?
                        (bool?)null : Convert.ToBoolean(sdr["Readed"]),
                        Sex = sdr["Sex"] == DBNull.Value ?
                        null : Convert.ToString(sdr["Sex"]),
                    });
                }
            }
            return list;
        }
        /// <summary>
        /// 条件查询总记录数
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="Index"></param>
        /// <param name="stu"></param>
        /// <param name="sdate"></param>
        /// <param name="edate"></param>
        /// <returns></returns>
        public int Proc_SelectPageCountWhere(View_Stu_Class stu, string sdate, string edate)
        {
            string Pro_name = @"[dbo].[PS_proc_CommonPager]";
            List<View_Stu_Class> list = new List<View_Stu_Class>();
            List<SqlParameter> sps = new List<SqlParameter>() {
                new SqlParameter("@tblName","View_Stu_Class"),
                new SqlParameter("@fldName","ID"),
                new SqlParameter("@PageSize",0),
                new SqlParameter("@PageIndex",0),
                new SqlParameter("@IsCount",1),
                new SqlParameter("@OrderType",0),
            };
            StringBuilder Where = new StringBuilder("");
            if (stu != null)
            {
                if (!string.IsNullOrEmpty(stu.Name))
                {
                    Where.AppendFormat(" and name like '%{0}%'", stu.Name);
                }//字符串列类型判断
                if (!string.IsNullOrEmpty(stu.Sex))
                {
                    Where.AppendFormat(" and sex='{0}'", stu.Sex);
                }//数字类型判断
                if (stu.CID != null)
                {
                    Where.AppendFormat(" and CID='{0}'", stu.CID);
                }
            }
            if (!string.IsNullOrEmpty(sdate) && !string.IsNullOrEmpty(edate))
            {
                Where.AppendFormat(" and Birthday between '{0}' and '{1}'", sdate, edate);
            }
            sps.Add(new SqlParameter("@strWhere", Where.ToString()));
            object obj = DBHelper.Proc_ExecuteScalar(Pro_name, sps.ToArray());
            return Convert.ToInt32(obj);

        }
    }
}
